/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Config.ConnectionConfig;
import Config.Constants;
import java.math.BigInteger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.List;
import productJAXB.generate.ProductList;
import productJAXB.generate.Products;

/**
 *
 * @author DIEM PHUONG
 */
public class UserDAO {
    /*-----------------------------AUTHENTICATION----------------------------------------------*/
    // check for existing user or not

    public boolean checkUser(String username) {
        Connection con = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // get user from db by username
            String sqlStatement = "Select username From Users Where username = ?";
            PreparedStatement preparedStm = con.prepareStatement(sqlStatement);
            preparedStm.setString(1, username);
            // get result
            ResultSet rs = preparedStm.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return false;
    }
    // create a new account for new user

    public boolean register(String username, String password, String fullname, boolean gender,
            String phone, String email, String address) {
        Connection con = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // insert new record for new user into db
            String sqlStatement = "Insert Into Users(username, password, fullname, gender, phone, email, address, roleID, isActive)"
                    + " Values (?,?,?,?,?,?,?,'2','true')";
            PreparedStatement preparedstm = con.prepareStatement(sqlStatement);
            preparedstm.setString(1, username);
            preparedstm.setString(2, password);
            preparedstm.setString(3, fullname);
            preparedstm.setBoolean(4, gender);
            preparedstm.setString(5, phone);
            preparedstm.setString(6, email);
            preparedstm.setString(7, address);
            // get result
            int rs = preparedstm.executeUpdate();
            if (rs > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return false;
    }

    // check log in by username and pwd
    public String[] login(String username, String password) throws ClassNotFoundException, SQLException {
        //open connection
        Connection con = null;
        con = ConnectionConfig.getConnection();
        //get user by using username and password
        String sqlStatement = "Select * From Users INNER JOIN Roles ON Users.roleID = Roles.roleID"
                + " Where username = ? and password = ?";
        PreparedStatement preparedStm = con.prepareStatement(sqlStatement);
        preparedStm.setString(1, username);
        preparedStm.setString(2, password);
        //get result
        ResultSet rs = preparedStm.executeQuery();
        String[] result = new String[5];
        if (rs.next()) {
            result[0] = Constants.USR_SUCCESSFUL;
            result[1] = rs.getString("username");
            result[2] = rs.getString("fullname");
            result[3] = new Boolean(rs.getBoolean("isActive")).toString();
            result[4] = rs.getString("roleName");
        } else {
            result[0] = Constants.USR_FAILED;
        }
        // close connection
        if (rs != null) {
            rs.close();
        }
        if (preparedStm != null) {
            preparedStm.close();
        }
        if (con != null) {
            con.close();
        }
        return result;
    }

    // change password
    public static boolean changePassword(String username, String oldPass, String newPass) throws ClassNotFoundException, SQLException {
        // open connection
        Connection con = null;
        con = ConnectionConfig.getConnection();
        // get oldPassword
        String sqlStatement = "Select password From Users Where username = ? and password = ?";
        PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, oldPass);
        // get result
        ResultSet rs = preparedStatement.executeQuery();
        // if this user is existing --> update newPass --> success -- true
        if (rs.next()) {
            String sqlStm = "Update Users Set password = ? where username = ?";
            PreparedStatement preparedStm = con.prepareStatement(sqlStm);
            preparedStm.setString(1, newPass);
            preparedStm.setString(2, username);
            int rs1 = preparedStm.executeUpdate();
            if (rs1 > 0) {
                return true;
            }
            return false;
        }
        // else --> error
        return false;
    }

    /*-------------------------------------USERS------------------------------------*/
    // get address of user by username
    public static String getAddress(String username) throws SQLException {
        String address = Constants.BLANK_VALUE;
        Connection con = null;
        ResultSet rs = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // get address
            String sqlStatement = "Select address From Users Where username = ?";
            PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
            preparedStatement.setString(1, username);
            // get result
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                address = rs.getString("address");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // close connection
            if (rs != null) {
                rs.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return address;
    }

    // get email of user by username
    public static String getEmail(String username) throws SQLException {
        String email = Constants.BLANK_VALUE;
        Connection con = null;
        ResultSet rs = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // get email
            String sqlStatement = "Select email from Users Where username = ?";
            PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
            preparedStatement.setString(1, username);
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                email = rs.getString("email");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // close connection
            if (rs != null) {
                rs.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return email;
    }

    // get  account information of user into string --> split by ";"
    public static String accountInfo(String username) throws ClassNotFoundException, SQLException {
        String accountInfo = Constants.BLANK_VALUE;
        Connection con = null;
        ResultSet rs = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // get account info
            String sqlStatement = "Select fullname, phone, email, address From Users Where username = ?";
            PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
            preparedStatement.setString(1, username);
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                String fullname = rs.getString("fullname");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                String address = rs.getNString("address");
                accountInfo = fullname + ";" + username + ";" + phone + ";" + email + ";" + address;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // close connection
            if (rs != null) {
                rs.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return accountInfo;
    }

    //update account information of user
    public static String updateAccountInfo(String username, String phone, String email, String address) throws SQLException {
        String accountInfo = Constants.BLANK_VALUE;
        Connection con = null;
        ResultSet rs1 = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // update phone, email, address
            String sqlStatement = "Update Users Set phone = ?, email = ?, address = ? Where username = ?";
            PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
            preparedStatement.setString(1, phone);
            preparedStatement.setString(2, email);
            preparedStatement.setString(3, address);
            preparedStatement.setString(4, username);
            int rs = preparedStatement.executeUpdate();
            if (rs > 0) {
                String sqlStm = "Select fullname, phone, email, address From Users Where username = ?";
                PreparedStatement preparedStm = con.prepareStatement(sqlStm);
                preparedStm.setString(1, username);
                rs1 = preparedStm.executeQuery();
                if (rs1.next()) {
                    String fullname = rs1.getString("fullname");
                    phone = rs1.getString("phone");
                    email = rs1.getString("email");
                    address = rs1.getString("address");
                    accountInfo = fullname + ";" + username + ";" + phone + ";" + email + ";" + address;
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // close connection
            if (rs1 != null) {
                rs1.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return accountInfo;
    }

    /*-------------------------------PRODUCTS---------------------------------------*/
    // get all products
    public static Products getAllProducts() throws SQLException {
        Products products = new Products();
        Connection con = null;
        ResultSet rs = null;
        try {
            //open connection
            con = ConnectionConfig.getConnection();
            // get all products
            String sqlStatement = "Select * From Products";
            PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                int productID = rs.getInt("productID");
                String productName = rs.getString("productName");
                int categoryID = rs.getInt("categoryID");
                long price = rs.getLong("price");
                String productImg = rs.getString("productImg");
                String description = rs.getString("description");
                Boolean isShow = rs.getBoolean("isShow");
                int quantityInStock = rs.getInt("quantityInStock");

                if (productImg == null) {
                    productImg = "No picture";
                }
                if (description == null) {
                    description = "No description";
                }
                ProductList product = new ProductList();
                product.setProductID(new BigInteger(Integer.valueOf(productID).toString()));
                product.setProductName(productName);
                product.setCategoryID(new BigInteger(Integer.valueOf(categoryID).toString()));
                product.setPrice(new BigInteger(Long.valueOf(price).toString()));
                product.setProductImg(productImg);
                product.setDescription(description);
                product.setIsShow(isShow);
                product.setQuantityInStock(new BigInteger(Integer.valueOf(quantityInStock).toString()));
                products.getProduct().add(product);
            }
            return products;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // close connection
            if (rs != null) {
                rs.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    // get quantityInStock of product
    public static int getQuantityInStock(int productID) throws SQLException {
        int quantityInStock = Constants.ZERO_VALUE;
        Connection con = null;
        ResultSet rs = null;
        try {
            // open connection
            con = ConnectionConfig.getConnection();
            // get email by productID
            String sqlStatement = "Select quantityInStock From Products Where productID = ?";
            PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
            preparedStatement.setInt(1, productID);
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                quantityInStock = rs.getInt("quantityInStock");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // close connection
            if (rs != null) {
                rs.close();
            } if (con != null) {
                con.close();
            }
        }
        return quantityInStock;
    }

    // update quantityInStock after check out
    public static boolean updateQuantityInStock(int productID, int newQuantityInStock) throws ClassNotFoundException, SQLException {
        // open connection
        Connection con = null;
        ConnectionConfig config = new ConnectionConfig();
        con = config.getConnection();
        // update quantityInstock
        String sqlStatement = "Update Products Set quantityInStock = ? Where productID = ?";
        PreparedStatement preparedStatement = con.prepareStatement(sqlStatement);
        preparedStatement.setInt(1, newQuantityInStock);
        preparedStatement.setInt(2, productID);
        // get result
        int rs = preparedStatement.executeUpdate();
        if (rs > 0) {
            return true;
        }
        return false;
    }

    /*-----------------------------------ORDERS-----------------------------------*/

}
